1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # Author Joel Grand-Guillaume and Vincent Renaville Copyright 2013
6 # CSV data formating inspired from
7 # http://docs.python.org/2.7/library/csv.html?highlight=csv#examples
9 # This program is free software: you can redistribute it and/or modify
10 # it under the terms of the GNU Affero General Public License as
11 # published by the Free Software Foundation, either version 3 of the
12 # License, or (at your option) any later version.
14 # This program is distributed in the hope that it will be useful,
15 # but WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 # GNU Affero General Public License for more details.
19 # You should have received a copy of the GNU Affero General Public License
20 # along with this program. If not, see <http://www.gnu.org/licenses/>.
22 ##############################################################################
33 from openerp
.osv
import orm
, fields
34 from openerp
.tools
.translate
import _
37 class AccountUnicodeWriter(object):
40 A CSV writer which will write rows to CSV file "f",
41 which is encoded in the given encoding.
44 def __init__(self
, f
, dialect
=csv
.excel
, encoding
="utf-8", **kwds
):
45 # Redirect output to a queue
46 self
.queue
= cStringIO
.StringIO()
47 # created a writer with Excel formating settings
49 self
.writer
= csv
.writer(self
.queue
, dialect
=dialect
,
50 quoting
=csv
.QUOTE_ALL
, **kwds
)
52 self
.encoder
= codecs
.getincrementalencoder(encoding
)()
54 def writerow(self
, row
):
55 # we ensure that we do not try to encode none or bool
56 row
= (x
or u
'' for x
in row
)
59 c
.encode("utf-8") if isinstance(c
, unicode) else c
for c
in row
]
61 self
.writer
.writerow(encoded_row
)
62 # Fetch UTF-8 output from the queue ...
63 data
= self
.queue
.getvalue()
64 data
= data
.decode("utf-8")
65 # ... and reencode it into the target encoding
66 data
= self
.encoder
.encode(data
)
67 # write to the target stream
68 self
.stream
.write(data
)
70 self
.queue
.truncate(0)
72 def writerows(self
, rows
):
77 class AccountCSVExport(orm
.TransientModel
):
78 _name
= 'account.csv.export'
79 _description
= 'Export Accounting'
82 'data': fields
.binary('CSV', readonly
=True),
83 'company_id': fields
.many2one('res.company', 'Company',
85 'fiscalyear_id': fields
.many2one('account.fiscalyear', 'Fiscalyear',
87 'periods': fields
.many2many(
88 'account.period', 'rel_wizard_period',
89 'wizard_id', 'period_id', 'Periods',
90 help='All periods in the fiscal year if empty'),
91 'journal_ids': fields
.many2many(
97 help='If empty, use all journals, only used for journal entries'),
98 'account_ids': fields
.many2many(
100 'rel_wizard_account',
104 help='If empty, use all accounts, only used for journal entries'),
105 'export_filename': fields
.char('Export CSV Filename', size
=128),
108 def _get_company_default(self
, cr
, uid
, context
=None):
109 comp_obj
= self
.pool
['res.company']
110 return comp_obj
._company
_default
_get
(cr
, uid
, 'account.fiscalyear',
113 def _get_fiscalyear_default(self
, cr
, uid
, context
=None):
114 fiscalyear_obj
= self
.pool
['account.fiscalyear']
115 context
['company_id'] = self
._get
_company
_default
(cr
, uid
, context
)
116 return fiscalyear_obj
.find(cr
, uid
, dt
=None, exception
=True,
119 _defaults
= {'company_id': _get_company_default
,
120 'fiscalyear_id': _get_fiscalyear_default
,
121 'export_filename': 'account_export.csv'}
123 def action_manual_export_account(self
, cr
, uid
, ids
, context
=None):
124 this
= self
.browse(cr
, uid
, ids
)[0]
125 rows
= self
.get_data(cr
, uid
, ids
, "account", context
)
126 file_data
= StringIO
.StringIO()
128 writer
= AccountUnicodeWriter(file_data
)
129 writer
.writerows(rows
)
130 file_value
= file_data
.getvalue()
131 self
.write(cr
, uid
, ids
,
132 {'data': base64
.encodestring(file_value
)},
137 'type': 'ir.actions.act_window',
138 'res_model': 'account.csv.export',
142 'views': [(False, 'form')],
146 def _get_header_account(self
, cr
, uid
, ids
, context
=None):
154 def _get_rows_account(self
, cr
, uid
, ids
,
161 Return list to generate rows of the CSV file
164 select ac.code,ac.name,
165 sum(debit) as sum_debit,
166 sum(credit) as sum_credit,
167 sum(debit) - sum(credit) as balance
168 from account_move_line as aml,account_account as ac
169 where aml.account_id = ac.id
170 and period_id in %(period_ids)s
171 and journal_id in %(journal_ids)s
172 group by ac.id,ac.code,ac.name
175 {'fiscalyear_id': fiscalyear_id
,
176 'period_ids': tuple(period_range_ids
),
177 'journal_ids': tuple(journal_ids
)}
183 rows
.append(list(line
))
186 def action_manual_export_analytic(self
, cr
, uid
, ids
, context
=None):
187 this
= self
.browse(cr
, uid
, ids
)[0]
188 rows
= self
.get_data(cr
, uid
, ids
, "analytic", context
)
189 file_data
= StringIO
.StringIO()
191 writer
= AccountUnicodeWriter(file_data
)
192 writer
.writerows(rows
)
193 file_value
= file_data
.getvalue()
194 self
.write(cr
, uid
, ids
,
195 {'data': base64
.encodestring(file_value
)},
200 'type': 'ir.actions.act_window',
201 'res_model': 'account.csv.export',
205 'views': [(False, 'form')],
209 def _get_header_analytic(self
, cr
, uid
, ids
, context
=None):
210 return [_(u
'ANALYTIC CODE'),
219 def _get_rows_analytic(self
, cr
, uid
, ids
,
226 Return list to generate rows of the CSV file
228 cr
.execute(""" select aac.code as analytic_code,
229 aac.name as analytic_name,
231 sum(debit) as sum_debit,
232 sum(credit) as sum_credit,
233 sum(debit) - sum(credit) as balance
234 from account_move_line
235 left outer join account_analytic_account as aac
236 on (account_move_line.analytic_account_id = aac.id)
237 inner join account_account as ac
238 on account_move_line.account_id = ac.id
239 and account_move_line.period_id in %(period_ids)s
240 group by aac.id,aac.code,aac.name,ac.id,ac.code,ac.name
243 {'fiscalyear_id': fiscalyear_id
,
244 'period_ids': tuple(period_range_ids
)}
250 rows
.append(list(line
))
253 def action_manual_export_journal_entries(self
, cr
, uid
, ids
, context
=None):
255 Here we use TemporaryFile to avoid full filling the OpenERP worker
257 We also write the data to the wizard with SQL query as write seams
258 to use too much memory as well.
260 Those improvements permitted to improve the export from a 100k line to
262 with default `limit_memory_hard = 805306368` (768MB) with more lines,
263 you might encounter a MemoryError when trying to download the file even
264 if it has been generated.
266 To be able to export bigger volume of data, it is advised to set
267 limit_memory_hard to 2097152000 (2 GB) to generate the file and let
268 OpenERP load it in the wizard when trying to download it.
270 Tested with up to a generation of 700k entry lines
272 this
= self
.browse(cr
, uid
, ids
)[0]
273 rows
= self
.get_data(cr
, uid
, ids
, "journal_entries", context
)
274 with tempfile
.TemporaryFile() as file_data
:
275 writer
= AccountUnicodeWriter(file_data
)
276 writer
.writerows(rows
)
277 with tempfile
.TemporaryFile() as base64_data
:
279 base64
.encode(file_data
, base64_data
)
282 UPDATE account_csv_export
284 WHERE id = %s""", (base64_data
.read(), ids
[0]))
286 'type': 'ir.actions.act_window',
287 'res_model': 'account.csv.export',
291 'views': [(False, 'form')],
295 def _get_header_journal_entries(self
, cr
, uid
, ids
, context
=None):
297 # Standard Sage export fields
306 _(u
'FULL RECONCILE'),
307 _(u
'PARTIAL RECONCILE'),
308 _(u
'ANALYTIC ACCOUNT CODE'),
314 _(u
'AMOUNT CURRENCY'),
316 _(u
'ANALYTIC ACCOUNT NAME'),
323 _(u
'BANK STATEMENT'),
326 def _get_rows_journal_entries(self
, cr
, uid
, ids
,
333 Create a generator of rows of the CSV file
337 account_move_line.date AS date,
338 account_journal.name as journal,
339 account_account.code AS account_code,
340 res_partner.name AS partner_name,
341 account_move_line.ref AS ref,
342 account_move_line.name AS description,
343 account_move_line.debit AS debit,
344 account_move_line.credit AS credit,
345 account_move_reconcile.name as full_reconcile,
346 account_move_line.reconcile_partial_id AS partial_reconcile_id,
347 account_analytic_account.code AS analytic_account_code,
348 account_move.name AS entry_number,
349 account_account.name AS account_name,
350 account_move_line.debit - account_move_line.credit AS balance,
351 account_move_line.amount_currency AS amount_currency,
352 res_currency.name AS currency,
353 account_analytic_account.name AS analytic_account_name,
354 account_journal.name as journal,
355 account_period.code AS month,
356 account_fiscalyear.name as fiscal_year,
357 account_tax_code.code AS aml_tax_code_code,
358 account_tax_code.name AS aml_tax_code_name,
359 account_move_line.tax_amount AS aml_tax_amount,
360 account_bank_statement.name AS bank_statement
362 public.account_move_line
363 JOIN account_account on
364 (account_account.id=account_move_line.account_id)
365 JOIN account_period on
366 (account_period.id=account_move_line.period_id)
367 JOIN account_fiscalyear on
368 (account_fiscalyear.id=account_period.fiscalyear_id)
369 JOIN account_journal on
370 (account_journal.id = account_move_line.journal_id)
371 LEFT JOIN res_currency on
372 (res_currency.id=account_move_line.currency_id)
373 LEFT JOIN account_move_reconcile on
374 (account_move_reconcile.id = account_move_line.reconcile_id)
375 LEFT JOIN res_partner on
376 (res_partner.id=account_move_line.partner_id)
377 LEFT JOIN account_move on
378 (account_move.id=account_move_line.move_id)
379 LEFT JOIN account_tax on
380 (account_tax.id=account_move_line.account_tax_id)
381 LEFT JOIN account_tax_code on
382 (account_tax_code.id=account_move_line.tax_code_id)
383 LEFT JOIN account_analytic_account on
384 (account_analytic_account.id=account_move_line.analytic_account_id)
385 LEFT JOIN account_bank_statement on
386 (account_bank_statement.id=account_move_line.statement_id)
387 WHERE account_period.id IN %(period_ids)s
388 AND account_journal.id IN %(journal_ids)s
389 AND account_account.id IN %(account_ids)s
390 ORDER BY account_move_line.date
392 {'period_ids': tuple(period_range_ids
),
393 'journal_ids': tuple(journal_ids
),
394 'account_ids': tuple(account_ids
)}
397 # http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
398 # Set cursor.arraysize to minimize network round trips
400 rows
= cr
.fetchmany()
406 def get_data(self
, cr
, uid
, ids
, result_type
, context
=None):
407 get_header_func
= getattr(
408 self
, ("_get_header_%s" % (result_type
)), None)
409 get_rows_func
= getattr(self
, ("_get_rows_%s" % (result_type
)), None)
410 form
= self
.browse(cr
, uid
, ids
[0], context
=context
)
411 fiscalyear_id
= form
.fiscalyear_id
.id
413 period_range_ids
= [x
.id for x
in form
.periods
]
415 # If not period selected , we take all periods
416 p_obj
= self
.pool
.get("account.period")
417 period_range_ids
= p_obj
.search(
418 cr
, uid
, [('fiscalyear_id', '=', fiscalyear_id
)],
422 journal_ids
= [x
.id for x
in form
.journal_ids
]
424 j_obj
= self
.pool
.get("account.journal")
425 journal_ids
= j_obj
.search(cr
, uid
, [], context
=context
)
428 account_ids
= [x
.id for x
in form
.account_ids
]
430 aa_obj
= self
.pool
.get("account.account")
431 account_ids
= aa_obj
.search(cr
, uid
, [], context
=context
)
432 rows
= itertools
.chain((get_header_func(cr
, uid
, ids
,
434 get_rows_func(cr
, uid
, ids
,